package com.hz.control;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.hz.dbconn.DBConnect;
import com.hz.model.ParamDepartment;;


/**
 * 学院管理
 * 
 * @author wangp 创建日期:2007-05-30
 */
public class ParamDepartmentManager {

	/**
	 * 录入学院
	 * 
	 * @param role
	 *            学院实体
	 */
	public static boolean addDepartment(ParamDepartment department) {
		DBConnect dbc = new DBConnect();
		String sql = "insert into param_department(departmentNum,departmentName,departmentCampus,departmentSmallName,level) values(?,?,?,?,?)";
		dbc.prepareStatement(sql);
		try {
			dbc.setInt(1, department.getDepartmentNum());
			dbc.setString(2, department.getDepartmentName());	
			dbc.setString(3, department.getDepartmentCampus());
			dbc.setString(4, department.getDepartmentSmallName());
			dbc.setString(5, department.getLevel());
			dbc.executeUpdate();
			return true;
			
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			// 在这里一定要注意管理数据库连接
			dbc.close();
		}
	}

	/**
	 * 修改学院信息
	 * 
	 * @param id
	 *            学院唯一标识
	 * @param role
	 *            学院实体类
	 */
	public static boolean modifyDepartment(int id, ParamDepartment department) {
		DBConnect dbc = new DBConnect();
		String sql = "select * from param_department where id = " + id;
		dbc.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
		try {
			ResultSet rs = dbc.executeQuery();
			if (rs.next()) {
				rs.updateInt("departmentNum", department.getDepartmentNum());
				rs.updateString("departmentName", department.getDepartmentName());	
				rs.updateString("departmentCampus", department.getDepartmentCampus());
				rs.updateString("departmentSmallName", department.getDepartmentSmallName());	
				rs.updateString("level", department.getLevel());
				rs.updateRow();
				// 关闭记录集
				dbc.close(rs);
			} else {
				System.out.println("记录不存在!");
			}
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} finally {
			// 在这里一定要注意管理数据库连接
			dbc.close();
		}  

	}

	/**
	 * 删除一个学院
	 * 
	 * @param id
	 *            学院的ID号码
	 */
	public static void deleteDepartment(int id) {
		DBConnect dbc = new DBConnect();
		String sql = "delete from param_department where id = " + id;
		dbc.prepareStatement(sql);
		try {
			dbc.prepareStatement(sql);
			dbc.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			// 在这里一定要注意管理数据库连接
			dbc.close();
		}
	}
	
	/**
	 * 查询一个学院
	 * 
	 * @param id
	 *            学院的ID号码
	 */
	public static ParamDepartment findDepartment(Integer id) {
		DBConnect dbc = new DBConnect();
		ResultSet rs = null;
		ParamDepartment department=new ParamDepartment();
		String sql = "select * from param_department where id="+id;
		
		try {
			dbc.prepareStatement(sql);
			rs = dbc.executeQuery();
			if(rs.next())
			{
			   department.setId(id);
			   department.setDepartmentNum(rs.getShort("departmentNum"));
			   department.setDepartmentName(rs.getString("departmentName"));
			   department.setDepartmentCampus(rs.getString("departmentCampus"));
			   department.setDepartmentSmallName(rs.getString("departmentSmallName"));
			   department.setLevel(rs.getString("level"));
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			// 在这里一定要注意管理数据库连接
			dbc.close(rs);
			dbc.close();
		}
		return department;
	}
	
	/**
	 * 查询一个学院
	 * 
	 * @param id
	 *            学院的ID号码
	 */
	public static ParamDepartment findDepartmentByNum(int id) {
		DBConnect dbc = new DBConnect();
		ResultSet rs = null;
		ParamDepartment department=new ParamDepartment();
		String sql = "select * from param_department where departmentNum="+id;
		
		try {
			dbc.prepareStatement(sql);
			rs = dbc.executeQuery();
			if(rs.next())
			{
			   department.setId(id);
			   department.setDepartmentNum(rs.getShort("departmentNum"));
			   department.setDepartmentName(rs.getString("departmentName"));
			   department.setDepartmentCampus(rs.getString("departmentCampus"));
			   department.setDepartmentSmallName(rs.getString("departmentSmallName"));
			   department.setLevel(rs.getString("level"));
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			// 在这里一定要注意管理数据库连接
			dbc.close(rs);
			dbc.close();
		}
		return department;
	}
	
	/**
	 * 获取本科生或是研究生学院
	 * @param level
	 * @return
	 */
	public static ArrayList<ParamDepartment> getAllDepartmentWithlevel(String level) {
		DBConnect dbc = new DBConnect();
		ResultSet rs = null;
		ArrayList<ParamDepartment> departments=new ArrayList<ParamDepartment>();
		String clause = "";
		if(level!=null&&!level.equals(""))
			clause = " where level='"+level+"'";
		String sql = "select * from param_department "+clause+" order by departmentNum";
		
		try {
			dbc.prepareStatement(sql);
			rs = dbc.executeQuery();
			while(rs.next())
			{
			   ParamDepartment department=new ParamDepartment();
			   department.setId(rs.getInt("id"));
			   department.setDepartmentNum(rs.getShort("departmentNum"));
			   department.setDepartmentName(rs.getString("departmentName"));
			   department.setDepartmentCampus(rs.getString("departmentCampus"));
			   department.setDepartmentSmallName(rs.getString("departmentSmallName"));
			   departments.add(department);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			// 在这里一定要注意管理数据库连接
			dbc.close(rs);
			dbc.close();
		}
		return departments;
	}

	
	/**
	 * 查询某个学院
	 * 
	 * @param id
	 *            学院的ID号码
	 */
	public static List<ParamDepartment> getAllDepartment(String departmentId) {
		
		if(departmentId==null || departmentId.equals("0") || departmentId.equals("null")){
			departmentId="%";
		}
		
		DBConnect dbc = new DBConnect();
		ResultSet rs = null;
		ArrayList<ParamDepartment> departments=new ArrayList<ParamDepartment>();
		String sql = "select * from param_department where id like '"+departmentId+"' order by departmentNum";
		
		try {
			dbc.prepareStatement(sql);
			rs = dbc.executeQuery();
			while(rs.next())
			{
			   ParamDepartment department=new ParamDepartment();
			   department.setId(rs.getInt("id"));
			   department.setDepartmentNum(rs.getShort("departmentNum"));
			   department.setDepartmentName(rs.getString("departmentName"));
			   department.setDepartmentCampus(rs.getString("departmentCampus"));
			   department.setDepartmentSmallName(rs.getString("departmentSmallName"));
			   departments.add(department);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			// 在这里一定要注意管理数据库连接
			dbc.close(rs);
			dbc.close();
		}
		return departments;
	}
	/**
	 * 查询所有学院
	 * 
	 * @param id
	 *            学院的ID号码
	 */
	public static ArrayList<ParamDepartment> getAllDepartment() {
		DBConnect dbc = new DBConnect();
		ResultSet rs = null;
		ArrayList<ParamDepartment> departments=new ArrayList<ParamDepartment>();
		String sql = "select * from param_department where level='0' order by departmentNum";
		
		try {
			dbc.prepareStatement(sql);
			rs = dbc.executeQuery();
			while(rs.next())
			{
			   ParamDepartment department=new ParamDepartment();
			   department.setId(rs.getInt("id"));
			   department.setDepartmentNum(rs.getShort("departmentNum"));
			   department.setDepartmentName(rs.getString("departmentName"));
			   department.setDepartmentCampus(rs.getString("departmentCampus"));
			   department.setDepartmentSmallName(rs.getString("departmentSmallName"));
			   departments.add(department);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			// 在这里一定要注意管理数据库连接
			dbc.close(rs);
			dbc.close();
		}
		return departments;
	}
	/**
	 * 查询所有学院，包括研究生
	 * 林龙
	 * @param id
	 *            学院的ID号码
	 */
	public static ArrayList<ParamDepartment> getAllDepartment(int eduLevel) {
		DBConnect dbc = new DBConnect();
		ResultSet rs = null;
		ArrayList<ParamDepartment> departments=new ArrayList<ParamDepartment>();
		String sql = "select * from param_department order by level";
		
		try {
			dbc.prepareStatement(sql);
			rs = dbc.executeQuery();
			while(rs.next())
			{
			   ParamDepartment department=new ParamDepartment();
			   department.setId(rs.getInt("id"));
			   department.setDepartmentNum(rs.getShort("departmentNum"));
			   department.setDepartmentName(rs.getString("departmentName"));
			   department.setDepartmentCampus(rs.getString("departmentCampus"));
			   department.setDepartmentSmallName(rs.getString("departmentSmallName"));
			   departments.add(department);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			// 在这里一定要注意管理数据库连接
			dbc.close(rs);
			dbc.close();
		}
		return departments;
	}

	/**
	 * 查询所有学院
	 * 
	 * @param id
	 *            学院的ID号码
	 */
	public static ArrayList<ParamDepartment> getAllDepartmentEdulevel(String eduLevel) {
		DBConnect dbc = new DBConnect();
		ResultSet rs = null;
		if(eduLevel==null || eduLevel.equals(""))
			eduLevel="%";
		
		ArrayList<ParamDepartment> departments=new ArrayList<ParamDepartment>();
		String sql = "select * from param_department where level like '"+eduLevel+"' order by departmentNum ";
		if(eduLevel.equals("1")){
			sql = "select * from param_department order by departmentNum ";
		}
		try {
			dbc.prepareStatement(sql);
			rs = dbc.executeQuery();
			while(rs.next())
			{
			   ParamDepartment department=new ParamDepartment();
			   department.setId(rs.getInt("id"));
			   department.setDepartmentNum(rs.getShort("departmentNum"));
			   department.setDepartmentName(rs.getString("departmentName"));
			   department.setDepartmentCampus(rs.getString("departmentCampus"));
			   department.setDepartmentSmallName(rs.getString("departmentSmallName"));
			   departments.add(department);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			// 在这里一定要注意管理数据库连接
			dbc.close(rs);
			dbc.close();
		}
		return departments;
	}
	public static ArrayList<ParamDepartment> getAllDepartmentWithoutEdulevel() {
		DBConnect dbc = new DBConnect();
		ResultSet rs = null;

		ArrayList<ParamDepartment> departments=new ArrayList<ParamDepartment>();
		String sql = "select * from param_department order by departmentNum ";
		
		try {
			dbc.prepareStatement(sql);
			rs = dbc.executeQuery();
			while(rs.next())
			{
			   ParamDepartment department=new ParamDepartment();
			   department.setId(rs.getInt("id"));
			   department.setDepartmentNum(rs.getShort("departmentNum"));
			   department.setDepartmentName(rs.getString("departmentName"));
			   department.setDepartmentCampus(rs.getString("departmentCampus"));
			   department.setDepartmentSmallName(rs.getString("departmentSmallName"));
			   department.setLevel(rs.getString("level"));
			   departments.add(department);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			// 在这里一定要注意管理数据库连接
			dbc.close(rs);
			dbc.close();
		}
		return departments;
	}
	
	/**
	 * 返回学院编号
	 * 
	 * @param departmentName
	 *            学院唯一标识	 
	 */
	public static int DepartmentId(String departmentName) {
		int result=0;
		DBConnect dbc = new DBConnect();
		String sql = "select id as deptId from param_department where departmentName like '" + departmentName+"'";
		
		dbc.prepareStatement(sql);
		try {
			ResultSet rs = dbc.executeQuery();
			if (rs.next()) {
				result=rs.getInt("deptId");
				System.out.println("id ="+result);
				
			} else {
				System.out.println("记录不存在!");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			// 在这里一定要注意管理数据库连接
			dbc.close();
		}
  return result;
	}

	
	
	/**
	 * 返回学院编号
	 * 
	 * @param departmentName
	 *            学院唯一标识	 
	 */
	public static String Departmenta(String departmentName) {
		String result="0";
		DBConnect dbc = new DBConnect();
		String sql = "select * from param_department where departmentName = '" + departmentName+" '";
		dbc.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
		try {
			ResultSet rs = dbc.executeQuery();
			if (rs.next()) {
				result=rs.getString("departmentName");
				dbc.close(rs);
			} else {
				System.out.println("记录不存在!");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			// 在这里一定要注意管理数据库连接
			dbc.close();
		}
  return result;
	}

	
	
	
	
	
	
	
	
	
}
